/**  定义旅社相关的接口 */
const express = require('express');
const router = express.Router();
const Joi = require('joi');
const Response = require('../utils/Response.js');

// 引入mysql连接池
const pool = require('../utils/db.js');

// 查看所有设施
router.get('/hotel/services', (req, resp) => {
  let sql = 'select * from services';
  pool.query(sql, (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    resp.send(Response.ok(result));
  });
});

// 预定日期 添加，删除，查看，修改状态

/**
 * 通过id、日期修改预订状态接口
 * @param:
 *   status: 2--预定过期
 *   hm_id
 * @return:
 *   {code:200, msg:'ok'}
 */
router.post('/hotel/update/time', (req, resp) => {
  let { book_date, status, id } = req.body;
  // 表单验证
  let schema = Joi.object({
    book_date: Joi.string().required(), // 必填
    status: Joi.number().required(), // 必填
    id: Joi.number().required(), // 必填
  });
  let { error, value } = schema.validate(req.body);
  if (error) {
    resp.send(Response.error(400, error));
    return; // 结束
  }
  // 执行删除业务
  let sql = `update opening_time set status=? where hm_id=?`;
  pool.query(sql, [book_date, status, id], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    resp.send(Response.ok());
  });
});

/**
 * 通过hm_id添加预订日期
 * @param:
 *   详见接口文档
 * @return:
 *   {code:200, msg:'ok'}
 */
router.post('/hotel/add/time', (req, resp) => {
  let { book_date, status, id } = req.body; // post请求参数在req.body中
  // 表单验证
  let schema = Joi.object({
    book_date: Joi.string(),
    status: Joi.string(),
    id: Joi.number(),
  });
  let { error, value } = schema.validate(req.body);
  if (error) {
    resp.send(Response.error(400, error));
    return; // 结束
  }
  // 表单验证通过，执行添加操作
  let sql = `insert into opening_time(book_date, status, hm_id) values(?,?,?)`;
  pool.query(sql, [book_date, status, id], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    resp.send(Response.ok());
  });
});

/**
 * 通过ID查询预订时间信息
 * @param:
 *   id:   旅社hmid
 * @return:
 *   {code:200, msg:'ok', data:{}}
 */
router.get('/hotel/query/time', (req, resp) => {
  let { id } = req.query;
  // 表单验证
  let schema = Joi.object({
    id: Joi.string().required(), // 必填
  });
  let { error, value } = schema.validate(req.query);
  if (error) {
    resp.send(Response.error(400, error));
    return; // 结束
  }

  // 执行查询业务
  let sql = `SELECT * FROM opening_time WHERE hm_id=?`;
  pool.query(sql, [id], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    if (result && result.length == 0) {
      // 没查到
      resp.send(Response.ok(null));
    } else {
      resp.send(Response.ok(result));
    }
  });
});

/**
 * 通过状态status、id删除预订时间接口
 * @param:
 *   status: 3--预定过期
 *   hmid
 * @return:
 *   {code:200, msg:'ok'}
 */
router.post('/hotel/del/time', (req, resp) => {
  let { status, id } = req.body;
  // 表单验证
  let schema = Joi.object({
    status: Joi.number().required(), // 必填
    id: Joi.number().required(), // 必填
  });
  let { error, value } = schema.validate(req.body);
  if (error) {
    resp.send(Response.error(400, error));
    return; // 结束
  }
  // 执行删除业务
  let sql = `delete from opening_time where status=? and hm_id=?`;
  pool.query(sql, [status, id], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    resp.send(Response.ok());
  });
});

// -----------------------------

/**
 * 通过id添加旅社房型价格接口
 * @param:
 *   详见接口文档
 * @return:
 *   {code:200, msg:'ok'}
 */
router.post('/hotel/add/price', (req, resp) => {
  let { bigroom, doubleroom, singleroom, specialroom, id } = req.body; // post请求参数在req.body中
  // 表单验证
  let schema = Joi.object({
    bigroom: Joi.string(),
    doubleroom: Joi.string(),
    singleroom: Joi.string(),
    specialroom: Joi.string(),
    id: Joi.number(),
  });
  let { error, value } = schema.validate(req.body);
  if (error) {
    resp.send(Response.error(400, error));
    return; // 结束
  }
  // 表单验证通过，执行添加操作
  let sql = `insert into hotel_price(bigroom, doubleroom, singleroom, specialroom, hm_id) values(?,?,?,?,?)`;
  pool.query(
    sql,
    [bigroom, doubleroom, singleroom, specialroom, id],
    (error, result) => {
      if (error) {
        resp.send(Response.error(500, error));
        throw error;
      }
      resp.send(Response.ok());
    }
  );
});

/**
 * 通过id修改旅社房型价格接口
 * @param:
 *   详见接口文档
 * @return:
 *   {code:200, msg:'ok'}
 */
router.post('/hotel/update/price', (req, resp) => {
  let { bigroom, doubleroom, singleroom, specialroom, id } = req.body; // post请求参数在req.body中
  // 表单验证
  let schema = Joi.object({
    bigroom: Joi.string(),
    doubleroom: Joi.string(),
    singleroom: Joi.string(),
    specialroom: Joi.string(),
    id: Joi.number(),
  });
  let { error, value } = schema.validate(req.body);
  if (error) {
    resp.send(Response.error(400, error));
    return; // 结束
  }
  // 表单验证通过，执行添加操作
  let sql = `update hotel_price set bigroom=?, doubleroom=?, singleroom=?, specialroom=? where hm_id=? `;
  pool.query(
    sql,
    [bigroom, doubleroom, singleroom, specialroom, id],
    (error, result) => {
      if (error) {
        resp.send(Response.error(500, error));
        throw error;
      }
      resp.send(Response.ok());
    }
  );
});

/**
 * 通过ID查询旅社价格信息
 * @param:
 *   id:   旅社hmid
 * @return:
 *   {code:200, msg:'ok', data:{}}
 */
router.get('/hotel/query/price', (req, resp) => {
  let { id } = req.query;
  // 表单验证
  let schema = Joi.object({
    id: Joi.string().required(), // 必填
  });
  let { error, value } = schema.validate(req.query);
  if (error) {
    resp.send(Response.error(400, error));
    return; // 结束
  }

  // 执行查询业务
  let sql = `SELECT * FROM hotel_price WHERE hm_id=?`;
  pool.query(sql, [id], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    if (result && result.length == 0) {
      // 没查到
      resp.send(Response.ok(null));
    } else {
      resp.send(Response.ok(result));
    }
  });
});

// ----------------------------------------

/**
 * 通过id添加旅社图片接口
 * @param:
 *   hmid旅社信息
 * @return:
 *   {code:200, msg:'ok'}
 */
router.post('/hotel/add/img', (req, resp) => {
  let {
    display1,
    display2,
    display3,
    display4,
    display5,
    room_big,
    room_double,
    room_single,
    room_special,
    id,
  } = req.body; // post请求参数在req.body中
  // 表单验证
  let schema = Joi.object({
    display1: Joi.string(),
    display2: Joi.string(),
    display3: Joi.string(),
    display4: Joi.string(),
    display5: Joi.string(),
    room_big: Joi.string(),
    room_single: Joi.string(),
    room_double: Joi.string(),
    room_special: Joi.string(),
    id: Joi.number(),
  });
  let { error, value } = schema.validate(req.body);
  if (error) {
    resp.send(Response.error(400, error));
    return; // 结束
  }
  // 表单验证通过，执行添加操作
  let sql = `insert into hotel_img(display1,display2,display3,display4,display5,room_big,room_double,room_single,room_special,hm_id) values(?,?,?,?,?,?,?,?,?,?)`;
  pool.query(
    sql,
    [
      display1,
      display2,
      display3,
      display4,
      display5,
      room_big,
      room_double,
      room_single,
      room_special,
      id,
    ],
    (error, result) => {
      if (error) {
        resp.send(Response.error(500, error));
        throw error;
      }
      resp.send(Response.ok());
    }
  );
});

/**
 * 通过id修改旅社图片接口
 * @param:
 *   详见接口文档
 * @return:
 *   {code:200, msg:'ok'}
 */
router.post('/hotel/update/img', (req, resp) => {
  let {
    display1,
    display2,
    display3,
    display4,
    display5,
    room_big,
    room_double,
    room_single,
    room_special,
    id,
  } = req.body; // post请求参数在req.body中
  // 表单验证
  let schema = Joi.object({
    display1: Joi.string(),
    display2: Joi.string(),
    display3: Joi.string(),
    display4: Joi.string(),
    display5: Joi.string(),
    room_big: Joi.string(),
    room_single: Joi.string(),
    room_double: Joi.string(),
    room_special: Joi.string(),
    id: Joi.number(),
  });
  let { error, value } = schema.validate(req.body);
  if (error) {
    resp.send(Response.error(400, error));
    return; // 结束
  }
  // 表单验证通过，执行添加操作
  let sql = `update hotel_img set
  display1=?,
  display2=?,
  display3=?,
  display4=?,
  display5=?,
  room_big=?,
  room_double=?,
  room_single=?,
  room_special=? where hm_id=? `;
  pool.query(
    sql,
    [
      display1,
      display2,
      display3,
      display4,
      display5,
      room_big,
      room_double,
      room_single,
      room_special,
      id,
    ],
    (error, result) => {
      if (error) {
        resp.send(Response.error(500, error));
        throw error;
      }
      resp.send(Response.ok());
    }
  );
});

/**
 * 通过ID查询旅社图片
 * @param:
 *   id:   旅社hmid
 * @return:
 *   {code:200, msg:'ok', data:{}}
 */
router.get('/hotel/query/img', (req, resp) => {
  let { id } = req.query;
  // 表单验证
  let schema = Joi.object({
    id: Joi.string().required(), // 必填
  });
  let { error, value } = schema.validate(req.query);
  if (error) {
    resp.send(Response.error(400, error));
    return; // 结束
  }

  // 执行查询业务
  let sql = `SELECT hi.* FROM hotel_message hm LEFT JOIN hotel_img hi ON hi.hm_id=hm.hmid WHERE hm.hmid=?`;
  pool.query(sql, [id], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    if (result && result.length == 0) {
      // 没查到
      resp.send(Response.ok(null));
    } else {
      resp.send(Response.ok(result));
    }
  });
});

// ---------------------------------------------

/**
 * 通过id删除旅社信息接口
 * @param:
 *   id:   hmid
 * @return:
 *   {code:200, msg:'ok'}
 */
router.post('/hotel/del', (req, resp) => {
  let { id } = req.body;
  // 表单验证
  let schema = Joi.object({
    id: Joi.string().required(), // 必填
  });
  let { error, value } = schema.validate(req.body);
  if (error) {
    resp.send(Response.error(400, error));
    return; // 结束
  }
  // 执行删除业务
  let sql = `delete
    from 
    hotel_message
    where hmid =?`;
  pool.query(sql, [id], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    resp.send(Response.ok());
  });
});

/**
 * 通过ID查询旅社信息
 * @param:
 *   id:   旅社hmid
 * @return:
 *   {code:200, msg:'ok', data:{}}
 */
router.get('/hotel/query/info', (req, resp) => {
  let { id } = req.query;
  // 表单验证
  let schema = Joi.object({
    id: Joi.string().required(), // 必填
  });
  let { error, value } = schema.validate(req.query);
  if (error) {
    resp.send(Response.error(400, error));
    return; // 结束
  }

  // 执行查询业务
  let sql = `
  SELECT hm.* FROM hotel_message hm WHERE hm.hmid=?`;
  pool.query(sql, [id], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    if (result && result.length == 0) {
      // 没查到
      resp.send(Response.ok(null));
    } else {
      resp.send(Response.ok(result[0]));
    }
  });
});

/**
 * 通过地区查询旅社信息
 * @param:
 *   district:   旅社所在地区
 * @return:
 *   {code:200, msg:'ok', data:{}}
 */
router.get('/hotel/query/district', (req, resp) => {
  let { district } = req.query;
  // 表单验证
  let schema = Joi.object({
    district: Joi.string().required(), // 必填
  });
  let { error, value } = schema.validate(req.query);
  if (error) {
    resp.send(Response.error(400, error));
    return; // 结束
  }
  // 执行查询业务
  let sql = `SELECT * FROM hotel_message,hotel_price where district=? and hmid=hpid`;
  pool.query(sql, [district], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    if (result && result.length == 0) {
      // 没查到
      resp.send(Response.ok(null));
    } else {
      resp.send(Response.ok(result));
    }
  });
});

/**
 * 查询所有旅社信息(分页)
 * @param:
 *   {page,pagesize}
 * @return:
 *   {code:200, msg:'ok', data:[{}]}
 */
router.get('/hotel', async (req, resp) => {
  // 获取请求参数   get请求的参数封装在req.query中
  let { page, pagesize } = req.query;

  //TODO 服务端表单验证  如果验证通过那么继续后续业务  如果验证不通过，则直接返回参数异常
  let schema = Joi.object({
    page: Joi.number().required(), // page必须是数字，必填
    pagesize: Joi.number().integer().max(100).required(), // pagesize必须是不大于100的数字，必填
  });
  let { error, value } = schema.validate(req.query);
  if (error) {
    resp.send(Response.error(400, error));
    return; // 结束
  }
  // 执行查询数组业务
  try {
    let startIndex = (page - 1) * pagesize;
    let size = parseInt(pagesize);
    let sql =
      'select * from hotel_message,hotel_price where hmid=hpid limit ?,?';
    let result = await pool.querySync(sql, [startIndex, size]);
    // 执行查询总条目数
    let sql2 =
      'select count(*) as count from hotel_message,hotel_price where hmid=hpid';
    let result2 = await pool.querySync(sql2, [startIndex, size]);
    let total = result2[0].count;
    resp.send(
      Response.ok({ page: parseInt(page), pagesize: size, total, result })
    );
  } catch (error) {
    resp.send(Response.error(error));
  }
});

/**
 * 通过选定日期与所属地区查询有关旅社预订的旅社信息列表
 *
 */
router.get('/hotel/date', (req, resp) => {
  let { book_date, district } = req.query;
  // 表单验证
  let schema = Joi.object({
    book_date: Joi.string().required(),
    district: Joi.string().required(),
  });
  let { error, value } = schema.validate(req.query);
  if (error) {
    resp.send(Response.error(400, error));
    return; // 结束
  }
  // 执行查询
  let sql = `select * from hotel_message 
       inner join hotel_price  
       on hotel_message.hmid = hotel_price.hm_id
       inner join opening_time
       on hotel_message.hmid=opening_time.hm_id
       where hotel_message.district=?
       and opening_time.book_date=?
      and opening_time.status=1
      GROUP BY hotel_message.hmid;`;
  pool.query(sql, [district, book_date], (error, result) => {
    if (error) {
      resp.send(Response.error(500, error));
      throw error;
    }
    resp.send(Response.ok(result));
  });
});

/**
 * 添加旅社信息接口
 * @param:
 *   详见接口文档
 * @return:
 *   {code:200, msg:'ok'}
 */
router.post('/hotel/add', (req, resp) => {
  let {
    code,
    tel,
    cover,
    hname,
    city,
    district,
    addr,
    email,
    services,
    hmdetail,
    longitude,
    latitude,
  } = req.body; // post请求参数在req.body中

  // 表单验证
  let schema = Joi.object({
    code: Joi.string().required(),
    tel: Joi.string(),
    email: Joi.string(),
    cover: Joi.string().required(),
    hname: Joi.string().required(),
    city: Joi.string().required(),
    district: Joi.string().required(),
    addr: Joi.string().required(),
    services: Joi.string().required(),
    hmdetail: Joi.string().required(),
    longitude: Joi.number().required(),
    latitude: Joi.number().required(),
  });
  let { error, value } = schema.validate(req.body);
  if (error) {
    resp.send(Response.error(400, error));
    return; // 结束
  }

  // 表单验证通过，执行添加操作
  let sql = `insert into hotel_message 
        (code,
          tel,
          cover,
          hname,
          city,
          district,
          addr,
          email,
          services,
          hmdetail,
          longitude,
          latitude) 
            values 
        (?,?,?,?,?,?,?,?,?,?,?,?)`;
  pool.query(
    sql,
    [
      code,
      tel,
      cover,
      hname,
      city,
      district,
      addr,
      email,
      services,
      hmdetail,
      longitude,
      latitude,
    ],
    (error, result) => {
      if (error) {
        resp.send(Response.error(500, error));
        throw error;
      }
      // 获取当前添加旅社的ID，并初始化预订日期表
      let insertId = result.insertId;
      // 处理时间
      let date = new Date();
      let y = date.getFullYear();
      let m = date.getMonth() + 1;
      m = m < 10 ? '0' + m : m;
      let d = date.getDate();
      d = d < 10 ? '0' + d : d;
      let book_date = `${y}-${m}-${d}`;
      // 执行添加
      let sql2 =
        'insert into opening_time (book_date,front_desk_time,check_in,status,hm_id) values (?,?,?,?,?)';
      pool.query(
        sql2,
        [book_date, '10:00 到 18:00', '10:00后', 2, insertId],
        (error2, result2) => {
          if (error2) {
            resp.send(Response.error(500, error2));
            throw error2;
          }
        }
      );
      // 初始化价格表
      let sql3 =
        'insert into hotel_price (bigroom,doubleroom,singleroom,specialroom,hm_id) values(?,?,?,?,?)';
      pool.query(
        sql3,
        ['200.00', '200.00', '200.00', '200.00', insertId],
        (error3, result3) => {
          if (error3) {
            resp.send(Response.error(500, error3));
            throw error3;
          }
          resp.send(Response.ok());
        }
      );
    }
  );
});

/**
 * 修改旅社信息接口
 * @param:
 *   详见接口文档
 * @return:
 *   {code:200, msg:'ok'}
 */
router.post('/hotel/update', (req, resp) => {
  let {
    hmid,
    code,
    tel,
    cover,
    hname,
    city,
    district,
    addr,
    email,
    services,
    hmdetail,
    longitude,
    latitude,
  } = req.body; // post请求参数在req.body中
  // 表单验证
  let schema = Joi.object({
    email: Joi.string(),
    tel: Joi.string(),
    code: Joi.string().required(),
    cover: Joi.string().required(),
    hname: Joi.string().required(),
    city: Joi.string().required(),
    district: Joi.string().required(),
    addr: Joi.string().required(),
    services: Joi.string().required(),
    hmdetail: Joi.string().required(),
    longitude: Joi.number().required(),
    latitude: Joi.number().required(),
    hmid: Joi.number().required(),
  });
  let { error, value } = schema.validate(req.body);
  if (error) {
    resp.send(Response.error(400, error));
    return; // 结束
  }
  // 表单验证通过，执行添加操作
  let sql = `update hotel_message set 
      code=?, 
      tel=?,
      email=?,
      cover=?, 
      hname=?, 
      city=?, 
      district=?, 
      addr=?, 
      services=?, 
      hmdetail=?, 
      longitude=?, 
      latitude=?
    where hmid=?`;
  pool.query(
    sql,
    [
      code,
      tel,
      email,
      cover,
      hname,
      city,
      district,
      addr,
      services,
      hmdetail,
      longitude,
      latitude,
      hmid,
    ],
    (error, result) => {
      if (error) {
        resp.send(Response.error(500, error));
        throw error;
      }
      resp.send(Response.ok());
    }
  );
});

// 将router对象导出
module.exports = router;
